package com.ifnk.portaljava.hlyt.dao.impl;

import com.ifnk.portaljava.dapWeb.advance.DBAccess;
import com.ifnk.portaljava.hlyt.beans.Log;
import com.ifnk.portaljava.hlyt.dao.ILogJdbcDao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class LogJdbcDaoImpl implements ILogJdbcDao {

	public int save(Log log, Connection conn) throws Exception {
		Statement stmt = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int id = 0;
		try {
			String idSql = "select max(id) as maxId from log";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(idSql);
			while (rs.next()) {
				id = rs.getInt("maxId") + 1;
			}
			conn.setAutoCommit(false);
			String sql = "insert into log(id,level,time,user_name,user_ip,module_name,event,info,result) values(?,?,now(),?,?,?,?,?,?)";
			System.out.println("insert sql:" + sql);
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.setString(2, log.getLevel());
			pstmt.setString(3, log.getUserName());
			pstmt.setString(4, log.getUserIp());
			pstmt.setString(5, log.getModuleName());
			pstmt.setString(6, log.getEvent());
			pstmt.setString(7, log.getInfo());
			pstmt.setString(8, log.getResult());
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("save log error!");
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return id;

	}

	public List<String> findUserName() throws Exception {
		DBAccess db = new DBAccess();
		String querySql = "select distinct user_name from log where user_name is not null order by id asc;;";
		System.out.println("querySql:" + querySql);
		ResultSet rs = db.queryTable(querySql);
		List<String> userNameList = new ArrayList<String>();
		String userName;
		try {
			while (rs.next()) {
				userName = rs.getString("user_name");
				userNameList.add(userName);
			}
		} catch (SQLException e) {
			throw new Exception("find user name error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return userNameList;
	}

	public List<Log> findByDate(String dateString) throws Exception {
		DBAccess db = new DBAccess();
		String querySql = "select id,level,time,user_name,user_ip,module_name,event,info,result from log where time like '%"
				+ dateString + "%' order by id asc;";
		System.out.println("querySql:" + querySql);
		ResultSet rs = db.queryTable(querySql);
		List<Log> logList = new ArrayList<Log>();
		Log log = null;
		try {
			while (rs.next()) {
				log = new Log();
				log.setId(rs.getInt("id"));
				log.setLevel(rs.getString("level"));
				log.setTime(rs.getTimestamp("time"));
				log.setUserName(rs.getString("user_name"));
				log.setUserIp(rs.getString("user_ip"));
				log.setModuleName(rs.getString("module_name"));
				log.setEvent(rs.getString("event"));
				log.setInfo(rs.getString("info"));
				log.setResult(rs.getString("result"));
				logList.add(log);
			}
		} catch (SQLException e) {
			throw new Exception("find log by date error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return logList;
	}

	public List<String> findResult() throws Exception {
		DBAccess db = new DBAccess();
		String querySql = "select distinct result from log order by id asc;";
		System.out.println("querySql:" + querySql);
		ResultSet rs = db.queryTable(querySql);
		List<String> resultList = new ArrayList<String>();
		String result;
		try {
			while (rs.next()) {
				result = rs.getString("result");
				resultList.add(result);
			}
		} catch (SQLException e) {
			throw new Exception("find result error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return resultList;
	}

	public List<String> findModuleName() throws Exception {
		DBAccess db = new DBAccess();
		String querySql = "select distinct module_name from log order by id asc;";
		System.out.println("querySql:" + querySql);
		ResultSet rs = db.queryTable(querySql);
		List<String> moduleNameList = new ArrayList<String>();
		String moduleName;
		try {
			while (rs.next()) {
				moduleName = rs.getString("module_name");
				moduleNameList.add(moduleName);
			}
		} catch (SQLException e) {
			throw new Exception("find module name error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return moduleNameList;
	}

	public List<String> findLevel() throws Exception {
		DBAccess db = new DBAccess();
		String querySql = "select distinct level from log order by id asc;";
		System.out.println("querySql:" + querySql);
		ResultSet rs = db.queryTable(querySql);
		List<String> levelList = new ArrayList<String>();
		String level;
		try {
			while (rs.next()) {
				level = rs.getString("level");
				levelList.add(level);
			}
		} catch (SQLException e) {
			throw new Exception("find level error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return levelList;
	}

	public List<String> findEvent() throws Exception {
		DBAccess db = new DBAccess();
		String querySql = "select distinct event from log order by id asc;";
		System.out.println("querySql:" + querySql);
		ResultSet rs = db.queryTable(querySql);
		List<String> eventList = new ArrayList<String>();
		String event;
		try {
			while (rs.next()) {
				event = rs.getString("event");
				eventList.add(event);
			}
		} catch (SQLException e) {
			throw new Exception("find event error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return eventList;
	}

	public List<Log> findLogList(Log log, Timestamp startTime, Timestamp endTime)
			throws Exception {
		DBAccess db = new DBAccess();
		String querySql = "select id,level,time,user_name,user_ip,module_name,event,info,result from log where";
		String whereString = "";
		if (log.getUserName() != null) {
			if (whereString == "") {
				whereString = " user_name like '%" + log.getUserName() + "%'";
			} else {
				whereString += " and user_name like '%" + log.getUserName()
						+ "%'";
			}
		}

		if (log.getUserIp() != null) {
			if (whereString == "") {
				whereString = " user_ip like '%" + log.getUserIp() + "%'";
			} else {
				whereString += " and user_ip like '%" + log.getUserIp() + "%'";
			}
		}
		if (log.getModuleName() != null) {
			if (whereString == "") {
				whereString = " module_name like '%" + log.getModuleName()
						+ "%'";
			} else {
				whereString += " and module_name like '%" + log.getModuleName()
						+ "%'";
			}
		}
		if (log.getLevel() != null) {
			if (whereString == "") {
				whereString = " level like '%" + log.getLevel() + "%'";
			} else {
				whereString += " and level like '%" + log.getLevel() + "%'";
			}
		}
		if (log.getEvent() != null) {
			if (whereString == "") {
				whereString = " event like '%" + log.getEvent() + "%'";
			} else {
				whereString += " and event like '%" + log.getEvent() + "%'";
			}
		}
		if (log.getResult() != null) {
			if (whereString == "") {
				whereString = " result like '%" + log.getResult() + "%'";
			} else {
				whereString += " and result like '%" + log.getResult() + "%'";
			}
		}
		if (startTime != null) {
			if (whereString == "") {
				whereString = " time >= '" + startTime + "'";
			} else {
				whereString += " and time >= '" + startTime + "'";
			}
		}
		if (endTime != null) {
			if (whereString == "") {
				whereString = " time <= '" + endTime + "'";
			} else {
				whereString += " and time <= '" + endTime + "'";
			}
		}

		querySql = querySql + whereString + ";";

		System.out.println("querySql:" + querySql);
		ResultSet rs = db.queryTable(querySql);
		List<Log> logList = new ArrayList<Log>();
		Log l = null;
		try {
			while (rs.next()) {
				l = new Log();
				l.setId(rs.getInt("id"));
				l.setLevel(rs.getString("level"));
				l.setTime(rs.getTimestamp("time"));
				l.setUserName(rs.getString("user_name"));
				l.setUserIp(rs.getString("user_ip"));
				l.setModuleName(rs.getString("module_name"));
				l.setEvent(rs.getString("event"));
				l.setInfo(rs.getString("info"));
				l.setResult(rs.getString("result"));
				logList.add(l);
			}
		} catch (SQLException e) {
			throw new Exception("find log by date error!");
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return logList;
	}

	public static void main(String[] args) throws Exception {
		LogJdbcDaoImpl logDao = new LogJdbcDaoImpl();
		Log log = new Log();
		log.setEvent("Login");
		log.setUserName("zhxy");
		System.out.println(logDao.findLogList(log, null, null).size());
	}
}
